﻿using Kingdee.BOS;
using Kingdee.BOS.App.Core;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Log;
using Kingdee.BOS.Model.ReportFilter;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Serialization;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
namespace LiMinCRMPlugin
{
    /// <summary>
    /// 【执行计划】获取并保存账表数据
    /// https://vip.kingdee.com/article/109345564135072000
    /// </summary>
    [Description("调用应收款账龄分析表，保存数据")]
    [Kingdee.BOS.Util.HotUpdate]
    public class AR_AgingAnalysisPlan : IScheduleService
    {

        /// <summary>
        /// 执行计划
        /// </summary>
        /// <param name="ctx"></param>
        /// <param name="schedule"></param>
        public void Run(Context ctx, Schedule schedule)
        {

            if (schedule.Parameters.IsNullOrEmpty())
            {
                return;
            }



            var parameters = JsonConvert.DeserializeObject<List<RptHelper.SaveReportDataParameter>>(schedule.Parameters);
            foreach (var parameter in parameters)
            {
                try
                {
                    RptHelper.SaveReportData(ctx, parameter);
                }
                catch (Exception ex)
                {
                    var errMsg = string.Format("读取报表数据异常:{0}", JsonConvert.SerializeObject(parameter));
                    Logger.Error("Jac", errMsg, ex);
                }
            }
        }
    }
    /// <summary>
    /// 账表辅助类
    /// </summary>
    public class RptHelper
    {
        #region method

        /// <summary>
        /// 保存账表数据
        /// </summary>
        /// <param name="scheduleCtx">上下文</param>
        /// <param name="parameter">保存账表数据参数</param>
        /// <param name="typeId"></param>
        public static void SaveReportData(Context scheduleCtx, SaveReportDataParameter parameter, string typeId = "0")
        {
            var ctx = GetContext(scheduleCtx, parameter.UserName);
            // 读取账表数据（存货收发存汇总表）
            var reportData = GetReportData(ctx, parameter);
            // 将账表数据保存到指定的表中
            #region 方式1：直接在数据库拷贝一份，如果不再加工数据，推荐用此方式
            if (typeId == "0")
            {
                var sql = string.Format(@"/*dialect*/
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'{0}') AND type IN (N'U'))
DROP TABLE {0}
SELECT * INTO {0} FROM {1}", parameter.TableName, reportData.DataSource.TableName);
                DBUtils.Execute(ctx, sql);

                var sql_update = string.Format(@"/*dialect*/ update {0} set FEndDate=fdate,fdate=getdate()", parameter.TableName);
                //var sql_update = string.Format(@"/*dialect*/ update {0} set FEndDate=fdate,fdate='2024-11-30'", parameter.TableName);
                DBUtils.Execute(ctx, sql_update);


                var sql_Salebody = string.Format(@"/*dialect*/     update b set f_dev_salebody=(select fname from RDLV_t_sale_body_L where fid=a.F_RDLV_SALEBODY)
                  from V_AgingAnalysis_receivable a,
                  Data_AgingAnalysis b
                  where  FCUSTOMERID in
                  (select fcustid from T_BD_CUSTOMER where FNUMBER=b.fcontactunitnumber) and a.FCURRENCYID=b.fcurrencyid and a.FSETTLEORGID=b.FSettleOrgID
                  and b.f_dev_salebody is null", parameter.TableName);

                DBUtils.Execute(ctx, sql_Salebody);

                var sql_delete = string.Format(@"/*dialect*/ delete from  T_Data_AgingAnalysis where FORMAT(CAST(F_QLTK_ModifyDate AS DATETIME), 'yyyy-MM-dd')='" + DateTime.Now.ToString("yyyy-MM-dd")+"'");
                DBUtils.Execute(ctx, sql_delete);

                var sql_Inert = string.Format(@"/*dialect*/ insert into T_Data_AgingAnalysis select * from V_Insert_AgingAnalysis");
                DBUtils.Execute(ctx, sql_Inert);

            }
            #endregion
            #region 方式2：使用内存表批量插入数据库
            else if (typeId == "1")
            {
                // 创建临时表
                var createTableSql = BuildCreateTableSql(reportData.DataSource, parameter.TableName);
                DBUtils.Execute(ctx, createTableSql);
                // 将账表数据存入临时表
                var dataSource = reportData.DataSource.Clone();
                dataSource.TableName = parameter.TableName;
                foreach (DataRow row in reportData.DataSource.Rows)
                {
                    dataSource.Rows.Add(row.ItemArray);
                }
                DBUtils.BulkInserts(ctx, dataSource);
            }
            #endregion
        }
        /// <summary>
        /// 获取简单账表数据
        /// </summary>
        /// <param name="ctx">上下文</param>
        /// <param name="parameter">账表数据保存参数</param>
        /// <returns></returns>
        public static IReportData GetReportData(Context ctx, SaveReportDataParameter parameter)
        {
            var metaDataService = new MetaDataService();
            var filterMetadata = new CommonFilterService().GetFilterMetaData(ctx, ""); //加载过滤元数据。
            var reportMetadata = (FormMetadata)metaDataService.Load(ctx, parameter.RptFormId); //加载账表元数据
            var reportFilterMetadata = (FormMetadata)metaDataService.Load(ctx, parameter.RptFilterFormId); //加载账表的过滤窗体的元数据。
            var reportFilterServiceProvider = reportFilterMetadata.BusinessInfo.GetForm().GetFormServiceProvider();
            var model = new SysReportFilterModel();
            model.SetContext(ctx, reportFilterMetadata.BusinessInfo, reportFilterServiceProvider);
            model.FormId = reportFilterMetadata.BusinessInfo.GetForm().Id;
            model.FilterObject.FilterMetaData = filterMetadata;
            model.InitFieldList(reportMetadata, reportFilterMetadata);
            model.GetSchemeList();
            var entity = model.Load(parameter.SchemeId);
            var dyn = DeserializeCustomFilter(reportFilterMetadata.BusinessInfo, entity.CustomFilterSetting);
            dyn["ByDate"] = DateTime.Now.ToString("yyyy-MM-dd");//赋值当前日期到查询条件
            //dyn["ByDate"] = "2025-08-27";//赋值当前日期到查询条件
            string sql = string.Format(@"/*dialect*/ select top 1 STUFF((SELECT ', ' + CONVERT(VARCHAR, FORGID) FROM T_ORG_Organizations  where F_QLTK_AgingAnalysis='1'   FOR XML PATH('')),    1, 2, '')  " +
                "from T_ORG_Organizations");
            DataTable dt = DBUtils.ExecuteDataSet(ctx, sql).Tables[0];


            dyn["SettleOrgLst"] = dt.Rows[0][0].ToString();//赋值所有组织到查询，防止有新增组织
            model.DataObject = dyn;
            var filter = model.GetFilterParameter();
            IRptParams rptParams = new RptParams();
            rptParams.FormId = reportMetadata.BusinessInfo.GetForm().Id;
            rptParams.FilterParameter = filter;
            rptParams.FilterFieldInfo = model.FilterFieldInfo;
            rptParams.ParameterData = GetUserParamters(ctx, reportMetadata.BusinessInfo);
            rptParams.IsOnlyQuerySumData = GetOnlyDspSumData(rptParams.ParameterData);
            rptParams.CurrentPosition = 0;
            rptParams.StartRow = 1;
            rptParams.EndRow = int.MaxValue;
            var param = new ReportServiceParameter(ctx, reportMetadata.BusinessInfo, Guid.NewGuid().ToString(), rptParams);
            return new SysReportService().GetReportData(param);
        }
        /// <summary>
        /// 获取指定用户上下文
        /// </summary>
        /// <param name="ctx">上下文</param>
        /// <param name="userName">用户名</param>
        /// <returns></returns>
        public static Context GetContext(Context ctx, string userName)
        {
            if (userName.IsNullOrEmpty())
            {
                return ctx;
            }
            var userId = DBUtils.ExecuteScalar(ctx, string.Format("SELECT FUSERID FROM T_SEC_USER WHERE FNAME='{0}'", userName), 0);
            if (userId <= 0)
            {
                return ctx;
            }
            var newCtx = (Context)ctx.Clone();
            newCtx.UserId = userId;
            newCtx.UserName = userName;
            return newCtx;
        }
        /// <summary>
        /// 获取过滤窗体的自定义参数
        /// </summary>
        /// <param name="businessInfo">过滤窗体元数据</param>
        /// <param name="xml">自定义过滤参数XML</param>
        /// <returns></returns>
        private static DynamicObject DeserializeCustomFilter(BusinessInfo businessInfo, string xml)
        {
            var binder = new DynamicObjectDcxmlBinder(businessInfo);
            binder.OnlyDbProperty = false;
            var target = new DcxmlSerializer(binder);
            binder.Culture = CultureInfo.InvariantCulture;
            target.ColloctionIgnorePKValue = true;
            var obj = (DynamicObject)target.DeserializeFromString(xml);
            return obj;
        }
        /// <summary>
        /// 获取账表的用户参数
        /// </summary>
        /// <param name="ctx">上下文</param>
        /// <param name="reportBusinessInfo"></param>
        /// <returns></returns>
        private static DynamicObject GetUserParamters(Context ctx, BusinessInfo reportBusinessInfo)
        {
            string formId = reportBusinessInfo.GetForm().ParameterObjectId;
            if (formId == null || formId.Trim().Length == 0)
            {
                formId = FormIdConst.BOS_ReportUserParameter;
            }
            var formMetadata = (FormMetadata)new MetaDataService().Load(ctx, formId);
            return new UserParameterService().Load(ctx, formMetadata.BusinessInfo, ctx.UserId, reportBusinessInfo.GetForm().Id);
        }
        /// <summary>
        /// 用户参数是否仅显示汇总
        /// </summary>
        /// <returns></returns>
        private static bool GetOnlyDspSumData(DynamicObject userParameterData)
        {
            var result = false;
            if (userParameterData != null && userParameterData.DynamicObjectType.Properties.ContainsKey(KeyConst.FDSPSUMDATA_KEY))
            {
                result = (bool)userParameterData[KeyConst.FDSPSUMDATA_KEY];
            }
            return result;
        }
        /// <summary>
        /// 根据DataTable构建建表脚本
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string BuildCreateTableSql(DataTable dt, string tableName)
        {
            var columnInfos = GetColumnInfos(dt);
            var sql = string.Format(@"/*dialect*/
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'{0}') AND type IN (N'U'))
DROP TABLE {0}
CREATE TABLE {0} ({1})", tableName, string.Join(",", columnInfos.Select(o => o.Item1 + " " + o.Item2)));
            return sql;
        }
        /// <summary>
        /// 根据DataTable生成创建列脚本
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static List<Tuple<string, string>> GetColumnInfos(DataTable dt)
        {
            var list = new List<Tuple<string, string>>();
            foreach (DataColumn column in dt.Columns)
            {
                list.Add(new Tuple<string, string>(column.ColumnName, GetDbType(column.DataType)));
            }
            return list;
        }
        /// <summary>
        /// 根据数据类型倒推字段的数据库类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static string GetDbType(Type type)
        {
            if (type == typeof(int) || type == typeof(short))
            {
                return "INT";
            }
            if (type == typeof(long))
            {
                return "BIGINT";
            }
            if (type == typeof(decimal) || type == typeof(float) || type == typeof(double))
            {
                return "DECIMAL(23,10)";
            }
            if (type == typeof(bool))
            {
                return "BIT";
            }
            if (type == typeof(DateTime))
            {
                return "DATETIME";
            }
            if (type == typeof(byte[]))
            {
                return "IMAGE";
            }
            return "NVARCHAR(4000)";
        }
        #endregion
        #region class
        /// <summary>
        /// 账表数据保存参数
        /// </summary>
        public class SaveReportDataParameter
        {
            /// <summary>
            /// 用户名(登录账号)
            /// </summary>
            public string UserName { get; set; }
            /// <summary>
            /// 账表FormId
            /// </summary>
            public string RptFormId { get; set; }
            /// <summary>
            /// 账表的过滤窗体的FormId
            /// </summary>
            public string RptFilterFormId { get; set; }
            /// <summary>
            /// 过滤方案内码
            /// </summary>
            public string SchemeId { get; set; }
            /// <summary>
            /// 存储账表数据的表名
            /// </summary>
            public string TableName { get; set; }
        }
        #endregion
    }
}



/*
-- 获取存货收发存汇总表下的demo用户的某个过滤方案
SELECT * FROM T_BAS_FILTERSCHEME WHERE FFORMID='HS_INOUTSTOCKSUMMARYRPT' AND FUSERID=100008
-- 获取指定用户的UserID
SELECT FUSERID FROM T_SEC_USER WHERE FNAME='demo'
*/
